import pandas as pd
import os,shutil
import class_mysql

# # 创建数据
# data = {
#     "主商家编码": ["5DT51787-HB"] * 8,
#     "规格商家编码": [
#         "5DT51787460DJ2", "5DT517874605DJ2", "5DT517874606DJ2", "5DT517874607DJ2",
#         "5DT517874608DJ2", "5DT517874609DJ2", "5DT517874610DJ2", "5DT517874611DJ2"
#     ],
#     "商品名称": ["两件装-童装短袖T恤"] * 8,
#     "颜色及规格": [
#         "混合色;4Y", "混合色;5Y", "混合色;6Y", "混合色;7Y",
#         "混合色;8Y", "混合色;9Y", "混合色;10Y", "混合色;11Y"
#     ],
#     "颜色属性": ["混合色"] * 8,
#     "第二属性": ["4Y", "5Y", "6Y", "7Y", "8Y", "9Y", "10Y", "11Y"]
# }

# # 创建 DataFrame
# df = pd.DataFrame(data)

# # 保存为 Excel 文件
# excel_path = "商品表.xlsx"
# df.to_excel(excel_path, index=False, sheet_name="商品数据")

# print(f"Excel 文件已生成: {excel_path}")
def get_file_paths(directory):
    file_paths = []
    for root, dirs, files in os.walk(directory):
        for file in files:
            file_paths.append(os.path.join(root, file))
    return file_paths

def main(path, category):
    path1=os.path.join(path,'上架图包')
    if not os.path.exists(path1):
        return
    folders=os.listdir(path1)
    db = class_mysql.MySQLQuery(host="43.161.240.94", user="hcecpod_com", password="Xcd9sMZ9DMBBkbMf", database="hcecpod_com")
    db.connect()
    datas = {"主商家编码": [],"规格商家编码": [],"商品名称": [],"颜色及规格": [],"颜色属性": [],"第二属性": []}
    for folder in folders:
        color=os.listdir(os.path.join(path1,folder))[0]
        style_name=folder.split('+')[0]+'#'+color
        results=db.fetch_all("select km_number,size from data_kuaimai_guangban where style_name = '"+style_name+"'")
        style_name=folder.split('+')[0]+'%'+color
        if len(results)==0:
            print(style_name)
            path_new=os.path.join(path,'结构性错误',style_name)
            path_old=os.path.join(path1,folder)
            if not os.path.exists(path_new):
                os.makedirs(path_new)
            shutil.move(path_old,path_new)
            path2=os.path.join(path,'快麦图包')
            file_paths=get_file_paths(path2)
            for file_path in file_paths:
                if style_name in file_path:
                    print(file_path,style_name)
                    folder_path=os.path.join(path,'结构性错误','快麦',style_name)
                    if not os.path.exists(folder_path):
                        os.makedirs(folder_path)
                    operater_number=file_path.split('+')[1]
                    pic_path=os.path.join(file_path.split('（图）')[0],operater_number)
                    pic_path_new=os.path.join(path,'结构性错误','快麦',style_name,operater_number)
                    shutil.move(pic_path,pic_path_new)
                    file_path_new=os.path.join(path,'结构性错误','快麦',style_name,os.path.basename(file_path))
                    shutil.move(file_path,file_path_new)
        else:
            # 获取第一个元素的 km_number
            first_km_number = results[0][0]

            # 创建两个新元素
            new_element1 = (first_km_number, 'xxxl')
            new_element2 = (first_km_number, 'xxxxxl')

            # 将新元素插入到 results 列表中
            results.append(new_element1)
            results.append(new_element2)
            for result in results:
                skc=folder
                sku=result['km_number']+'+'+folder.split('+')[1]
                name='一件装-'+category.replace('TEMU','')+'T恤'
                color_size=color+';'+result['size']
                size=result['size']
                datas['主商家编码'].append(skc)
                datas['规格商家编码'].append(sku)
                datas['商品名称'].append(name)
                datas['颜色及规格'].append(color_size)
                datas['颜色属性'].append(color)
                datas['第二属性'].append(size)
    df = pd.DataFrame(datas)
    excel_path = os.path.join(path,'表格','商品资料.xlsx')
    if not os.path.exists(os.path.dirname(excel_path)):
        os.makedirs(os.path.dirname(excel_path))
    try:
        df.to_excel(excel_path, index=False, sheet_name="Sheet1")
        print(f"Excel 文件已生成: {excel_path}")
    except:
        print("表格生成失败")